/**
* TS Client (http://www.transparent.co.nz)
* Copyright (c) 2004 Transparent Systems Limited
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the /doc/LICENSE.txt
* This is the GNU General Public License Version 2 as published by the Free Software Foundation.
* You can download this program from <a href="http://sourceforge.com/projects/ts-client">http://sourceforge.com/projects/ts-client</a>
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License Version 2 for more details.
*
* You should have received a copy of the GNU General Public License
* Version 2 along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
*
*/
/*
* Created on Nov 14, 2003
*
*/
package nz.co.transparent.client.controller;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import com.mckoi.database.jdbc.MckoiConnection;
import nz.co.transparent.client.db.ControllerException;
import nz.co.transparent.client.db.DataSourceHandler;
/**
* Handle specific request not handled by generic nz.co.transparent.client.controller
* Mostly related to substituting foreign keys.
* For performance reasons the queries are written hardcoded for each specific case
*
* @author johnz
*
*/
public class SpecificController {
private static SpecificController _instance;
private Logger log = Logger.getLogger("nz.co.transparent.client.db");
private DataSource dataSource = DataSourceHandler.getDataSource();
private SpecificController() {
}
public static SpecificController getInstance() {
if (_instance != null) {
return _instance;
}
_instance = new SpecificController();
return _instance;
}
/**
* Get contact details for selected client sorted on contact type
*
* @param clientID
* @return <code>List</code> with contact details
* @throws ControllerException
*/
public List findContactDetails(int clientID)
throws ControllerException {
List resultList = new ArrayList();
Map titleMap;
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
sql = "select * from contact_detail, contact_type";
sql += " where (";
sql += " (contact_detail.contact_type_id=contact_type.contact_type_id)";
sql += " and (client_id = ?)";
sql += " ) ORDER BY contact_type";
return (List) queryRunner.query(sql, new Integer(clientID), rsh);
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se); // wrap SQLException
}
}
/**
* Get default contact details for selected client
*
* @param clientID
* @return <code>Map</code>
* @throws ControllerException
*/
public Map findDefaultContactDetails(int clientID)
throws ControllerException {
DataSource dataSource = DataSourceHandler.getDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapHandler();
String sql = null;
try {
sql = "select * from contact_detail INNER JOIN contact_type ON (contact_detail.contact_type_id=contact_type.contact_type_id)";
sql += " where (";
sql += " (contact_detail.client_id=" + clientID + ")";
sql += " and (contact_type.is_default=true)";
sql += " )";
return (Map) queryRunner.query(sql, rsh);
} catch (SQLException se) {
String msg = "SQL error:\n" + se.getMessage();
log.warning(msg);
throw new ControllerException(msg);
}
}
/**
* Return primary key of title if titleCode present in list
* If titleCode is not present in list, than add record and return new primary key
*
* @param titleCode Title code to search for in list
* @param list List to be searched
* @return Primary key of found record or new record
* @throws ControllerException
*/
public Integer getForeignKeyTitle(String titleCode, List list)
throws ControllerException {
// Iterate over map and return primary key if title_code present in list
Map titleMap = new HashMap();
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
titleMap = (Map) iterator.next();
if (titleCode.equals(titleMap.get("title_code"))) {
Integer titleId = (Integer) titleMap.get("title_id");
return titleId;
}
}
// Add new title
GenericController controller = GenericController.getInstance();
titleMap = new HashMap();
titleMap.put("title_id", null); // Have key generated
titleMap.put("title_code", titleCode);
titleMap.put("title", titleCode);
titleMap.put("date_created", null);
titleMap.put("date_updated", null);
titleMap.put("updater_person_id", LoginController.getPerson().get("person_id"));
controller.insertRecord("title", "title_id", titleMap); // Will insert new key
list.add(titleMap);
return (Integer) titleMap.get("title_id");
}
/**
* Get payments
*
* @param invoiceID Invoice ID
* @return <code>List</code> with payments
* @throws ControllerException
*/
public List findPayments(int invoiceID)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
// Get connection from the connection pool
sql = "select * from payment, tender";
sql += " where (";
sql += " (payment.tender_id=tender.tender_id)";
sql += " and (invoice_id = ?)";
sql += " ) ORDER BY payment_date DESC";
return (List) queryRunner.query(sql, new Integer(invoiceID), rsh);
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se); // wrap SQLException
}
}
/**
* Get list of invoices with invoice details, payments and balance due
*
* @param clientID
* @return <code>List</code>
* @throws ControllerException
*/
public List findInvoices(int clientID)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler mapListRsh = new MapListHandler();
ResultSetHandler mapRsh = new MapHandler();
String sql = null;
try {
// Get connection from the connection pool
sql = "select * from invoice";
sql += " where (";
sql += " (client_id = ?)";
sql += " ) ORDER BY invoice_date DESC";
List invoiceList = (List) queryRunner.query(sql, new Integer(clientID), mapListRsh);
Iterator iterator = invoiceList.iterator();
Map invoiceMap = null;
Map paymentMap = null;
BigDecimal balanceDue = null;
BigDecimal paymentAmount = null;
String dollarAmount = null;
sql = "select sum(amount) as sum_amount from payment";
sql += " where (";
sql += " (invoice_id = ?)";
sql += " )";
while (iterator.hasNext()) {
invoiceMap = (Map) iterator.next();
paymentMap = (Map) queryRunner.query(sql, invoiceMap.get("invoice_id"), mapRsh);
balanceDue = invoiceMap.get("amount") == null ? new BigDecimal((double) 0) : (BigDecimal) invoiceMap.get("amount");
paymentAmount = paymentMap.get("sum_amount") == null ? new BigDecimal((double) 0) : (BigDecimal) paymentMap.get("sum_amount");
balanceDue = balanceDue.add(paymentAmount.negate());
invoiceMap.put("amount_paid", paymentAmount);
if (balanceDue.compareTo(new BigDecimal((double) 0)) == 0) {
invoiceMap.put("balance_due", null);
} else {
invoiceMap.put("balance_due", balanceDue);
}
}
return invoiceList;
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se); // wrap SQLException
}
}
/**
* Get person roles
*
* @param personID
* @return <code>List</code> with person roles
* @throws ControllerException
*/
public List findPersonRoles(int personID)
throws ControllerException {
List resultList = new ArrayList();
Map roleMap;
QueryRunner queryRunner = new QueryRunner(dataSource);
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
// Get connection from the connection pool
sql = "select * from person_role, role";
sql += " where (";
sql += " (person_role.role_id=role.role_id)";
sql += " and (person_id = ?)";
sql += " ) ORDER BY role";
return (List) queryRunner.query(sql, new Integer(personID), rsh);
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
}
}
/**
* Get clients for search parameters
*
* @param searchMap Map field {FieldName => FieldValue} pairs
* @param maxRecords
* @param maxPassed
* @return <code>List</code> with clients
* @throws ControllerException
*/
public List findClients(Map searchMap, int maxRecords, StringBuffer maxPassed)
throws ControllerException {
if (maxPassed.length() > 0) {
maxPassed.delete(0, maxPassed.length() -1);
}
List mapList = new ArrayList();
Map recordMap = null;
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rst = null;
ResultSetMetaData metaData = null;
String sql =null;
String columnName =null;
int numRecords = 0;
int i;
Set keySet = null;
Iterator iterator = null;
String key = null;
try {
DataSource dataSource = DataSourceHandler.getDataSource();
conn = dataSource.getConnection();
stmt = conn.createStatement();
stmt.setMaxRows(maxRecords + 1);
if (searchMap.get("contact_detail") == null || searchMap.get("contact_detail").equals("")) {
// Use LEFT JOIN
// Select only address type 1 (home/phone) if present
sql = "select * from client LEFT JOIN contact_detail ON (client.client_id=contact_detail.client_id AND contact_detail.contact_type_id=1)";
sql += " LEFT JOIN contact_type ON (contact_detail.contact_type_id=contact_type.contact_type_id)";
String whereClause = "";
keySet = searchMap.keySet();
iterator = keySet.iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
if (searchMap.get(key) != null && !searchMap.get(key).equals("")) {
if (!whereClause.equals("")) {
whereClause += " and ";
}
whereClause += " (client." + key + " like '%" + MckoiConnection.quote(searchMap.get(key).toString()) + "%')";
}
}
if (!whereClause.equals("")) {
sql += " where (";
sql += whereClause;
sql += ")";
}
sql += " ORDER BY last_name, first_name";
} else {
// Use INNER JOIN
sql = "select * from client, contact_detail, contact_type";
sql += " where (";
sql += " (client.client_id=contact_detail.client_id)";
sql += " and (contact_detail.contact_type_id=contact_type.contact_type_id)";
sql += " and (contact_detail.contact_detail like '%" + searchMap.get("contact_detail") + "%')";
keySet = searchMap.keySet();
iterator = keySet.iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
if (searchMap.get(key) != null && !searchMap.get(key).equals("")) {
if (key.equals("contact_detail")) {
sql += " and (contact_detail.";
} else {
sql += " and (client.";
}
sql += key + " like '%" + MckoiConnection.quote(searchMap.get(key).toString()) + "%')";
}
}
sql += ") ORDER BY last_name, first_name";
}
rst = stmt.executeQuery(sql);
metaData = rst.getMetaData();
int numColumns = metaData.getColumnCount();
while (rst.next()) {
numRecords++;
if (numRecords > maxRecords) {
maxPassed.append("y");
break;
}
// Create new recordMap
recordMap = new HashMap(numColumns);
// Iterate over columns starting at 1 !
for (i=1; i<=numColumns; i++) {
columnName = metaData.getColumnName(i);
Object valueObject = rst.getObject(i);
// If contact details are missing column is null
// If column already present, so not override
if (!recordMap.containsKey(columnName)) {
recordMap.put(columnName, valueObject);
}
}
mapList.add(recordMap);
//break; // TEST ONLY
}
return mapList;
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
} finally {
try {
DbUtils.close(rst);
DbUtils.close(stmt);
DbUtils.close(conn);
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
}
}
}
/**
* Get clients for search parameters
*
* @param searchMap Map with FieldName => FieldValue pairs
* @param maxRecords
* @param maxPassed
* @return <code>List</code> with clients
* @throws ControllerException
*/
public List findPersons(Map searchMap, int maxRecords, StringBuffer maxPassed)
throws ControllerException {
if (maxPassed.length() > 0) {
maxPassed.delete(0, maxPassed.length() -1);
}
List mapList = new ArrayList();
Map recordMap = null;
Connection conn = null;
Statement stmt = null;
ResultSet personResultSet = null;
ResultSetMetaData personMetaData = null;
String sql =null;
String columnName =null;
int numRecords = 0;
int i;
Set keySet = null;
Iterator iterator = null;
String key = null;
try {
DataSource dataSource = DataSourceHandler.getDataSource();
conn = dataSource.getConnection();
stmt = conn.createStatement();
sql = "select * from person";
String whereClause = "";
keySet = searchMap.keySet();
iterator = keySet.iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
if (searchMap.get(key) != null && !searchMap.get(key).equals("")) {
if (!whereClause.equals("")) {
whereClause += " and ";
}
whereClause += " (person." + key + " like '%" + searchMap.get(key) + "%')";
}
}
if (!whereClause.equals("")) {
sql += " where (";
sql += whereClause;
sql += ")";
}
sql += " ORDER BY user_name";
personResultSet = stmt.executeQuery(sql);
personMetaData = personResultSet.getMetaData();
int numColumns = personMetaData.getColumnCount();
while (personResultSet.next()) {
numRecords++;
if (numRecords > maxRecords) {
maxPassed.append("y");
break;
}
// Create new recordMap
recordMap = new HashMap(numColumns);
// Iterate over columns starting at 1 !
for (i=1; i<=numColumns; i++) {
columnName = personMetaData.getColumnName(i);
Object valueObject = personResultSet.getObject(i);
// If contact details are missing column is null
// If column already present, so not override
if (!recordMap.containsKey(columnName)) {
recordMap.put(columnName, valueObject);
}
}
mapList.add(recordMap);
//break; // TEST ONLY
}
return mapList;
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
} finally {
try {
DbUtils.close(personResultSet);
DbUtils.close(stmt);
DbUtils.close(conn);
} catch (SQLException se) {
String message = "SpecificController: SQL Exception: " + se.getMessage();
log.warning(message);
throw new ControllerException(se);
}
}
}
}